MySQL和他的儲存引擎InnoDB可以說是現今最常見的關聯式資料庫了，而可重複讀是他們預設的隔離等級。

但是，與Postgres相比，InnoDB在可重複讀的等級下有許多明顯問題。

1. 更新丟失(Lost Updates)
2. 幻讀(Phantom Reads)

更新丟失在Postgres可以完全避免而不需要任何手段，而幻讀有一些花招可以使用，例如range type或其他機制。

儘管如此，MySQL沒有這些特殊能力，所以必須小心的使用才有辦法閃過這些陷阱。在昨天，我們談過MySQL解決更新丟失的三種解法，那些解法可以有效運用在各種使用情境中。

在這篇文章中，我們會進一步了解如何解決寫入偏斜(Write Skew)所造成的幻讀。

造成幻讀的情境有很多種，但總體來說，他們都有一種固定的模式。

1. 搜尋一個特定範圍
2. 對這範圍的結果做一些操作(新增、修改、刪除)
3. 這些操作會直接影響到第一步搜尋出來的結果，例如本來10筆修改完後變成11筆

假設只是更新或刪除，最直覺避免寫入偏斜的作法就是上一個互斥鎖。在SELECT的時候使用FOR UPDATE就可以強制兩個同時的交易變成循序執行，進而避免競爭條件和寫入偏斜。

但是，若是新增，就沒這麼簡單了。因為，在尚未新增前不會有資料行可以上鎖。那麼該如何解決這個問題呢？

訂會議室系統

在介紹解法前，讓我們用一個實際的例子來解釋幻讀是如何造成的。

有一個訂會議室系統讓使用者可以預訂會議室。當使用者成功預訂，就會在booking資料表中增加一筆對應的登記資料，如下。

上表顯示有個使用者A訂了會議室123一個小時。

整個系統的行為會類似以下偽碼。

count = `SELECT COUNT(*) FROM booking 
         WHERE room_id = 123 AND 
         start_time < '2022-05-01 11:00' AND 
         end_time > '2022-05-01 10:00'`
         
if count == 0:
    `INSERT INTO booking (user, room_id, start_time, end_time)
     VALUES ('A', 123, '2022-05-01 10:00', '2022-05-01 11:00')`

當使用者確定對應的時間區間內沒有紀錄，使用者就插入一筆資料表示預訂，如此一來下個使用者就會知道有人預訂而不會產生衝突。

真的這麼簡單嗎？

當兩個使用者同時在預定同一個時段的同間會議室時，問題發生了！因為兩個人在SELECT時都看不到對方的紀錄，所以都會插入一筆資料，結果就會發生時間衝突。而且，這樣的情境是無法透過上鎖來避免的，因為根本沒有資料可以上鎖。

用唯一性來解(不完整解)

既然我們無法透過上鎖來強迫同時操作轉為循序操作，那麼我們只好讓其中一方直接失敗。要做到這樣就需要對資料表做些限制，例如唯一索引(unique constraints)。

我們可以在(room_id, start_time)這兩個欄位加上一個複合唯一索引，如此一來，較後面的那個操作會因為違反唯一性而失敗。

如果我們限制每個人一次都只能借一個小時的會議室，那麼問題已經解決了

但若是會議室可以被借用超過一個小時，就會產生另外一個有問題的情境。

1. 使用者A預訂5/1的10點至12點
2. 使用者B預訂5/1的11點至12點

當A和B同時操作，這個唯一索引明顯無法發揮作用，而時間衝突依然會發生。

衝突外部化

為了解決這樣的幻讀問題，我們必須用些手段來揭露這些隱藏的衝突。

其中一個作法是，創造一張新的表，並且預先填好所有的時間區間，然後使用這張表作為協調者。在我們會議室系統的例子中，我們可以創建time_slots這張表來預先存入所有會議室的所有可用時段。

當要預訂會議室時，我們除了要在booking執行SELECT以列出預訂外，同時也需要在time_slots上SELECT對應的時段，如此一來，我們就可以在time_slots上FOR UPDATE鎖。

值得一提的是，這個新的SELECT FOR UPDATE一定得要在原本的SELECT之前執行。

在這個案例中，若是兩個同時的使用者要預訂的時段衝突，那麼較後面的操作會被FOR UPDATE阻擋，進而使同時操作退化成循序操作。而當鎖釋放時，他也會看到前者已經預訂的紀錄就不會發生衝突了。

結論

我必須說，這樣的解法很困難也很不直覺。

但是為了在使用MySQL時不犧牲效能，我們不會將隔離等級設定為可序列化。也就是說，複雜度和效能兩者是必須進行取捨的。

事實上，透過FOR UPDATE來處理這樣的同步問題確實已經影響效能了。若是某一張資料表在所有的使用情境上都得要透過FOR UPDATE來確保不會有競爭條件，那麼還不如在一開始就把「這張表」的隔離等級設為可序列化，這樣也更省事。

當使用資料庫，我們必須了解資料庫的能耐，也必須認知資料庫無法解決的情境，這樣我們才能在設計和實作時知道哪些行為是有潛在威脅的。

此外，如何正確處理威脅也是一個重要的主題。雖然，每個人碰到的使用案例不一定相同，但是透過學習各種問題模式也可以幫助我們未來碰到相同情境時能更快發覺也更正確應對。

這篇文章提供了一個解決在「新增」資料時產生寫入偏斜的解法，而昨天的文章則是解決「更新」資料時產生的寫入偏斜。這樣應該就足以涵蓋大部分我們會碰到的使用情境了。